Fritz: All Fritz

home *** CD-ROM | disk | FTP | other *** search

/ Fritz: All Fritz / All Fritz.zip / All Fritz / FILES / ACCOUTIL / POWER123.LZH / ROR.DOC < prev next >

Wrap

Text File | 1985-10-08 | 26.4 KB | 661 lines

ROR - Rate of Return Calculator 1. INTRODUCTION This worksheet calculates the rate of return of a cash flow. Rate of return is notionally equivalent to the interest or discount rate that reduces the net present value of the cash flow to zero. (The net present value of a cash flow at different discount rates is calculated by the NPV worksheet.) Rate of return is one measure of the value of a cash flow that takes into account the time value of money (NPV is another measure). The higher the rate of return, the more you can discount future earnings and costs before the project becomes unprofitable. There are many ways to calculate rate of return, depending on your assumptions about financing sources and profit reinvestment opportunities. This worksheet uses three methods, described below. The PAYOUT worksheet (on the Volume 1B disk) calculates a project payout schedule, given the cash flow and interest rates for loans and investments. That worksheet takes into account the positive and negative balances accruing to the project. It also has a goal-seeking option to find the loan interest rate that reduces the project closing balance to zero. This is another way of calculating a rate of return for the project. 2. CALCULATION METHODS 2.1 Introduction As stated above, this worksheet uses three methods. Each method corresponds to a different set of assumptions about financing sources and profit reinvestment opportunities. The three methods typically lead to different values for the rate of return. This does not mean that one is right and the others are wrong; each one is correct according to its set of assumptions. The following descriptions include a discussion of the advantages and disadvantages of each method of calculation. 2.2 Internal Rate of Return The first (and simplest) method of calculating rate of return assumes that loans and investments make the same rate of return as the project being evaluated. For example, if the rate of return calculated by this method is 30%, the assumption is that the interest rate on money borrowed to finance this project and the yield obtained by reinvesting the profits are both 30% too. Nevertheless, this method of calculating rate of return is the most widely used. The result of this method is known as the internal rate of return (IRR) because it makes no reference to factors (such as discount or reinvestment rates) outside the cash flow. 09/09/84 ROR - 1 The IRR method may fall down when the cash flow is not 'well behaved'. The typical well-behaved cash flow has cash outflows at the beginning, followed by cash inflows. When there is more than one change of sign in the cash flow values, this method may yield multiple rates of return. Such cash flows can occur (for example) in income acceleration projects in the resource industries. Here, an initial investment leads to an increased cash flow in early years, but a reduced cash flow (over the 'do nothing' alternative) in later years as the accelerated resource depletion takes its toll. The cash flow for this project (compared with 'do nothing') starts out negative, becomes positive, then returns to negative again. Application of the simple method can yield two rates of return - one reasonable, and one ridiculously large. The large rate is mathematically correct, but assumes one could obtain that same rate of return by reinvesting the profits from the project, which is not usually practical. 2.3 Modified Internal Rate of Return The second method avoids the multiple rate of return problem by discounting all cash outflows to a single value in the opening period using a specified discount rate. This single value may be though of as the amount that would have to be invested now at the discount rate to support the outflows. The cash outflows are then replaced by this single outflow in period zero to give a modified cash flow (which is then 'well behaved'). The rate of return of this cash flow is calculated using the method described above. The assumption behind this method is that an amount is invested now at the discount rate and the proceeds are used to finance the cash outflows. The rate of return is the rate which this amount would have to yield (if invested) in order to return the same positive cash flows (in amount and timing) as the cash flow stream. 2.4 Financial Management Rate of Return The third method goes one step further. It begins, like the second method, by discounting all cash outflows to a single value in the opening period using the specified discount rate. Then it calculates the amount that would accrue in the final period if all cash inflows were reinvested at a specified reinvestment rate for the remainder of the project duration. The rate of return by this method is the rate which the single value in the opening period would have to yield (if invested) in order to accrue to the same amount as the reinvested cash inflow values yield in the final period. 2.4 General Comments There are many sets of assumptions which one could defend when evaluating a project. Each of the methods described above has its flaws. The first assumes that surpluses yield the same rate of return if reinvested (though supluses do not occur if the rate of return is applied to a well-behaved cash flow). The second and third methods do not take proper account of the 09/09/84 ROR - 2 deficits and surpluses that occur during the life of the project. The PAYOUT worksheet calculates a payout schedule for a project, given its cash flow and borrowing and reinvestment rates. This schedule calculates the closing balance for each period, and uses the appropriate rate depending on the sign of the balance. In this respect, it may be thought of as 'more accurate'. It also has a goal-seeking option, which yields the borrowing rate that reduces to zero the closing balance in the final period (ie. produces a breakeven project). Experimenting with the PAYOUT worksheet can give a better understanding of rate of return. The PAYOUT worksheet is contained on the Volume 1B disk in this series. 3. USING ROR 3.1 Overview The main menu of the ROR worksheet offers seven options (plus Quit). They are as follows: Input This option leads to a menu of the items you may input. This sub-menu allows you to enter the cash flow values by period, the number of periods corresponding to a year, the discount rate for cash outflows, the reinvestment rate for cash inflows, and a first estimate of the internal rate of return. The input process is described in more detail below. Blank This option blanks out all input data and results (after receiving confirmation that this is what you intend). It is useful for clearing the worksheet before entering an entirely new set of data. Calc The calculate option performs all calculations and displays the three rates of return. Results This shows the results of the calculations. If there are more than eleven periods in the cash flow (ie. it extends beyond the bottom of the home screen) this option yields a scroll menu that allows you to scroll the cash flow column up and down to view the entire cash flow. Graph Two graphs are available in this worksheet. One shows the cash flow and cumulative cash flow. The other shows the net present value of the cash flow at discount rates between 0 and 100 percent. Both graphs can be displayed in monochrome or color, as line charts, bar charts, or scatter (points only) graphs. 09/09/84 ROR - 3 Print The print option prints the cash flow, cumulative cash flow, and results area. Agenda If you select this option (and confirm it when requested), the current worksheet is lost and replaced by the worksheet selection agenda. From there, you can load another worksheet. Note confirmation is required before this option proceeds because it overwrites the worksheet in memory. 3.2 Input Details The Input option in the main menu leads to a sub-menu with five options - Cashflow, Periods/Yr, DiscRate, ReinvRate, and EstIRR. Each option allows you to enter part of the data. The Cashflow option allows you to enter cash flow values, by period. This option places the cell pointer at the top of the cash flow column, and moves it down one cell each time you press the Enter key. If you want to enter the entire cash flow, simply type each number in turn and press the Enter key after each one. Please note the following: - A value must be entered for each period covering the entire duration of the project and starting at period zero. If the cash flow value for a particular period is zero, enter zero. Do not leave the cell blank. - Period zero is now, period one is one period (year, month, or whatever) hence. - Cash outflows must be entered as negative numbers; cash inflows must be entered as positive numbers. If you don't want to enter the whole cash flow (for example, if you want to adjust a cash flow you've already entered) you may use the cursor movement keys to place the cell pointer on any number you want to change, and re-type it in normal 1-2-3 fashion. When you press the Enter key, the macro will move the cell pointer down one row. The macro which drives this process has no way of knowing when you've finished entering or changing data. When you have finished, you must break out of the macro and re-invoke the input menu. To do this, hold down the Ctrl key and press the Break key. The CTL flag at the top of the screen will go out. Then hold down the Alt key and press I to invoke the input menu. The Periods/Yr option accepts the number of periods that correspond to a year. If you have annual data, you must enter 1. If you have monthly data, enter 12. This parameter is used to convert the discount and reinvestment rates you enter from annual rates to rates per period. It is also used to convert the rates of return from rates per period to annual rates. Whatever the periods your data represents, you always enter rates, and read 09/09/84 ROR - 4 results as annual rates. The third choice in the input menu is DiscRate. It allows you to enter the annual rate used to discount cash outflows back to period zero. The fourth choice is ReinvRate. It allows you to enter the annual re-investment rate used to accumulate the future value of the cash inflows in the final period. The final option is EstIRR. Normally, you should leave this at zero, and the worksheet will choose its own first estimate of the internal rate of return. The worksheet will choose a value which is close to the first change of sign of the net present value. If the graph of net present value crosses the X-axis more than once, there is more than one internal rate of return. In this case, you may enter a discount rate close to the second crossing, and the worksheet will calculate its rate of return The last three options expect to receive only one number. To enter the number, simply select the option from the input menu, type the number, and press the Enter key. 3.3 Graph Details This worksheet can produce two different graphs on either the color or monochrome display. Each graph can take the form of a line graph, a bar chart, or a scatter graph (points without connecting lines). These are selected by means of a menu with four choices. The Cashflow graph displays the cash flow and cumulative cash flow by period. The form of the graph will be the same as the one that was last displayed. The NPV graph shows the net present value against the discount rate in steps of 5% from 0% to 100%. It shows how the net present value behaves as future values are discounted more or less heavily. The discount rate (if any) at which the graph crosses the X-axis represents the rate which reduces the net present value of the cash flow to zero. This is known as the internal rate of return of the cash flow. The Type option presents a sub-menu from which you can select the form of the graph. The three choices are Line, Bar, and Scatter. Each choice re-displays the last graph in the selected form, then returns to the graph menu. The fourth choice - Options - allows you to choose the Color or monochrome (B&W) monitor for the display. Again, each choice re-displays the last graph on the selected device. 4. EXAMPLES 4.1 A Simple Investment This example illustrates the use of this worksheet for a 09/09/84 ROR - 5 well-behaved cash flow. You have been given the opportunity to purchase a note for $15,000. The note will return $5,000 at the end of the first year, $7,000 at the end of the second year, and $9,000 at the end of the third year. What is the internal rate of return of this investment? Assuming you plan to re-invest the returns from the note at 13% until the end of the third year, what is the rate of return of this plan over the three year period. (Note, method two does not apply in this case, because there is only one cash outflow in period zero.) Keystroke Comments {ENTER} Select the Input option from the main menu. P 1 {ENTER} Number of periods per year (the data is annual) C Select the Cashflow option. The cell pointer will go to the top of the cash flow column. -15000 {ENTER} Value for period zero (now). Since this is a payment (ie. a cash outflow) it is entered as a negative number. When you press the ENTER key, the cell pointer will move down to the next cell in the column. 5000 {ENTER} Value returned at the end of the first year. This is a cash inflow. 7000 {ENTER} Value returned at the end of the second year. 9000 {ENTER} Value returned at the end of the third year. {Ctrl}{Break} To terminate the input loop, hold down the Ctrl key and press the Break key. {Alt} I To re-invoke the input menu, hold down the Alt key and press I. R 13 {ENTER} Re-investment rate, as an annual percentage. Q Return to the main menu. C Select the Calc option. The rate of return will be calculated using the three methods. G Select the Graph option from the main menu. text {ENTER} Whenever you enter the graph menu, the system will prompt you to enter a second title line for the graphs. You may leave this line blank, or enter any short description. To get rid of an existing description before typing a new one, press the Esc key. 09/09/84 ROR - 6 N This option displays a graph of the net present value against the discount rates. Press any key to return to the graph menu. T L If the graph is not already a line graph, you can go to the Type sub-menu to specify a line graph. The graph will be re-displayed. Press any key to return to the graph menu. 4.2 An Accelerated Income Project Many projects in the resources industry are aimed at accelerating resource recoveries. This leads to increased cash flows in early years, followed by compensating decreased cash flows in later years. Thus the cash flow consists of a series of outflows (the investment), followed by a series of inflows (the increased cash flows) followed by another series of outflows (the reduced cash flows). This is not a 'well-behaved' cash flow. You are considering the value of an enhanced recovery project. The immediate cost of this project is $600. It will increase cash flows in the next three years by $800, $500, and $200 respectively. In the three years after that, however, cash flows will be reduced by $200, $500, and $800 respectively. You need to know the rate of return of this project. Future cash outflows can be discounted at 10% p.a., and you can re-invest cash inflows at 18% p.a. Keystroke Comments {ENTER} Select the input option from the main menu. P 1 {ENTER} Number of periods per year (the data is annual). C Select the Cashflow option from the input menu. The cell pointer will go to the top cell of the cash flow column. -600 {ENTER} Initial investment (in period zero). The cell pointer will move down to the next period when you press the Enter key. 800 {ENTER} Increases in cash flows in the first three years. 500 {ENTER} 200 {ENTER} -200 {ENTER} Decreases in cash flows in the next three years. -500 {ENTER} -800 {ENTER} {Ctrl}{Break} Hold down the Ctrl key and press the Break key to break out of the macro. The CTL flag at the top of the screen will go out. 09/09/84 ROR - 7 Alt I To re-invoke the input menu, hold down the Alt key and press I. D 10 {ENTER} Select the DiscRate option in the input menu to enter the rate used to discount negative cash flows back to period zero. R 18 {ENTER} Select the ReinvRate option in the input menu, and enter the re-investment rate for positive cash flows. Q Return to the main menu. C Invoke the calculations. The results of the calculation show that the internal rate of return is 24%. But if you can only discount the future cash outflows at 10% p.a. (instead of the 24% that IRR assumes), the rate of return falls to almost zero. And even if you can guarantee an 18% return on the cash inflows, the rate of return is still only 13.05%. At this point, you might be intrigued about this cash flow. One way to look at it is to examine its net present value under different discount rates. To do this, enter the following: G Select the graph option. text {ENTER} Each time you invoke the graph menu, it asks you to enter the second title line for the graphs. Enter any short description. To remove an existing description before typing a new one, press the Esc key. N Select the NPV option in the graph menu to display a graph of the net present value against the annual discount rates. Press any key to return to the graph menu. T L Select a line graph. The graph will immediately be re-displayed. This graph shows that the net present value peaks at under $50 when the discount rate is around 40%. But this assumes that you can get a 40% rate of return on investments, which is unlikely. And even then, the net present value is very small. Clearly this is not a profitable project, even though the internal rate of return is impressive. You will also see that the net present value curve crosses the X-axis at two points. The worksheet will converge on the leftmost point, unless you enter a first estimate of the rate of return that is closer to the rightmost one. To find the rightmost rate of return, enter (say) 65 for EstIRR in the input menu, and repeat the calculations. To exit from the menus, type the following: 09/09/84 ROR - 8 Q Return to the main menu. Q Exit to 1-2-3 READY mode. If you want to return to the main menu, hold down the Alt key and type M. 09/09/84 ROR - 9 Menu Tree for ROR Worksheet. Input - Enter Cashflow Values and Discount/Reinvestment Rates . Periods/Yr - Enter number of periods per year . Cashflow - Enter cashflow values . DiscRate - Enter annual discount rate for cash outflows . ReinvRate - Enter annual reinvestment rate for cash inflows . Quit - Return to main menu Blank - Clear Input Fields . No - Cancel "Blank" Command . Yes - Yes, Erase all input Calc - Calculate Results Results - Show Results . Down - Scroll down one screen . Up - Scroll up one screen . Quit - Return to main menu Graph - Display Graphs . Cashflow - Display cashflow and cumulative cashflow . NPV - Display net present value vs. discount rate . Type - Select line graph, bar chart, or scatter plot . . Line - Display line graph . . Bar - Display bar chart . . Scatter - Display scatter graph . Options - Choose color or monochrome monitor . . Color - Display graphs in color . . B&W - Display graphs in monochrome . Quit - Return to Menu Print - Print Results Agenda - Return to Worksheet Selection Agenda . No - Do not clear this worksheet. Return to main menu . Yes - Clear this worksheet. Go to selection agenda Quit - Exit to 1-2-3 READY mode 09/09/84 ROR - 10